import datetime
import json
import sqlite3
import os

db_name = 'test.db'


def init_sqlite():
    try:
        # 数据库文件名
        db_filename = db_name

        # 检查数据库文件是否存在
        if not os.path.exists(db_filename):
            print(f"Database {db_filename} does not exist. Creating it now...")

            # 连接到SQLite数据库（如果不存在，它将被创建）
            conn = sqlite3.connect(db_filename)

            # 创建一个游标对象
            cursor = conn.cursor()

            # 你可以在这里执行一些SQL命令来创建表或插入数据
            # 例如，创建一个简单的表
            sql_create_table = '''  
            CREATE TABLE IF NOT EXISTS projects (  
                id INTEGER PRIMARY KEY,  
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,  
                ip_address TEXT NOT NULL,  
                device_info TEXT, 
                location TEXT  
            )  
            '''
            cursor.execute(sql_create_table)
            # 提交事务（如果你执行了写操作）
            conn.commit()

            # 关闭连接
            conn.close()

        print(f"Database {db_filename} is ready to use.")
        return True
    except:
        print(f"Database is fail.")
        return False

# ?ip_address=''&device_info=''&location=''
def INSERT(ip_address,device_info,location):
    try:
        # 连接到SQLite数据库
        conn = sqlite3.connect(db_name)
        cursor = conn.cursor()

        # 假设你已经有以下数据
        # ip_address = '192.168.1.1'
        # device_info = '{"model": "iPhone 12", "os": "iOS 14"}'  # 假设设备信息以JSON字符串形式存储
        # location = 'New York, USA'

        # 如果你想要手动设置时间戳（可选）
        current_time = datetime.datetime.now()
        timestamp = current_time.strftime('%Y-%m-%d %H:%M:%S')  # 转换为SQLite DATETIME格式

        # 如果没有手动设置时间戳，SQLite将使用DEFAULT CURRENT_TIMESTAMP
        sql_insert = '''  
        INSERT INTO projects (timestamp, ip_address, device_info, location)  
        VALUES (?, ?, ?, ?)  
        '''
        # 注意：我们没有包含id和timestamp字段，因为id是自动增长的，timestamp有默认值
        cursor.execute(sql_insert, (timestamp, ip_address, device_info, location))

        # 提交事务
        conn.commit()

        # 关闭连接
        conn.close()
        return ip_address
    except:
        return False


def DELETE():
    try:
        # 连接到SQLite数据库
        conn = sqlite3.connect(db_name)
        cursor = conn.cursor()

        # 删除一个用户
        sql_delete = '''DELETE FROM projects WHERE ip_address = ?'''
        cursor.execute(sql_delete, ('192.168.1.1',))

        # 提交事务
        conn.commit()

        # 关闭连接
        conn.close()
        return '192.168.1.1'
    except:
        return False


def UPDATE():
    try:
        # 连接到SQLite数据库
        conn = sqlite3.connect(db_name)
        cursor = conn.cursor()

        # 更新一个用户
        sql_update = '''UPDATE projects SET location = ? WHERE ip = ?'''
        cursor.execute(sql_update, ('Hubei', '192.168.1.1'))

        # 提交事务
        conn.commit()

        # 关闭连接
        conn.close()
        return 'Hubei', '192.168.1.1'
    except:
        return False


def SELECT():
    try:
        output = ''
        # 连接到SQLite数据库
        conn = sqlite3.connect(db_name)
        cursor = conn.cursor()

        # 查询最新的10个用户（基于timestamp降序排序）
        sql_select = '''SELECT id, timestamp, ip_address, device_info, location FROM projects ORDER BY timestamp DESC 
        LIMIT 10'''
        cursor.execute(sql_select)

        # 获取所有行，并将它们转换为字典列表
        rows = cursor.fetchall()
        columns = [column[0] for column in cursor.description]  # 获取列名
        results = [dict(zip(columns, row)) for row in rows]  # 使用zip和dict转换行数据为字典

        # 关闭连接
        conn.close()

        # 将字典列表转换为JSON字符串
        output = json.dumps(results, ensure_ascii=False, indent=4)  # indent使JSON更易读
        return output
    except:
        return False
